package dlc.persistencia;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
import java.sql.SQLException;
import dlc.indexado.lectura.DefaultListener;
import dlc.indexado.lectura.Listener;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Map;
import java.sql.Connection;
import dlc.modelo.Documento;
import dlc.modelo.Palabra;
import dlc.modelo.Posteo;

public class BaseDatos {

    SqlConnector connector;

    public BaseDatos(int valor) {//1-Scraping: pisa BD, 2-Archivador: no pisa BD

        try {
            connector = new SqlConnector();
            connector.connect();


        } catch (Exception e) {
            System.out.println("Error en la Coneccion a la BD");
            System.out.println(e.toString());
        }
        if (valor == 1) {
            reiniciarBD();
        }
    }

    private void reiniciarBD() {
        try {
           // connector.connect();
            this.connector.executeStatement("delete from links;");
            this.connector.executeStatement("alter TABLE links AUTO_INCREMENT=1;");
            this.connector.executeStatement("delete from posteos;");
            this.connector.executeStatement("alter TABLE posteos AUTO_INCREMENT=1;");
            this.connector.executeStatement("delete from vocabulario;");
            this.connector.executeStatement("alter TABLE vocabulario AUTO_INCREMENT=1;");
            this.connector.executeStatement("delete from documento;");
            this.connector.executeStatement("alter TABLE documento AUTO_INCREMENT=1;");
           // this.connector.disconnect();
        } catch (Exception ex) {
            System.out.println("ERROR en reiniciar la BD");
            System.out.println(ex.toString());
        }

    }

    //##################### Metodos usados en el modulo de scraping ##############################################################################
    public boolean buscarURL(String url) {
        try {
           // connector.connect();
            ResultSet result = connector.executeStatementQuery("select * from links where link like '" + url + "';");

            result.last();
            int a = result.getRow();

            if (result.getRow() == 0) {
                return false;
            } else {
                return true;
            }
        } catch (Exception ex) {
            System.out.println("Error en la lectura de la tabla de links");
        }
        return false;
    }

    public boolean agregarURL(String url) {
        try {
           // connector.connect();
            this.connector.executeStatement("insert into links (link) values ('" + url + "');");
           //  this.connector.disconnect();
            return true;
        } catch (Exception ex) {
            System.out.println("Error al insertar dato");
            System.out.println(ex.toString());
            return false;
        }

    }

    public String retornarURL(int i) {
        try {
           // connector.connect();
            ResultSet result = connector.executeStatementQuery("select link from links where idlinks=" + i + ";");
            result.last();
            if (result.getRow() == 1) {
                return result.getString("link");
            }
            
           // this.connector.disconnect();

            return "";

        } catch (Exception ex) {
            System.out.println("ERROR al leer la URL en la base de datos");
            System.out.println(ex.toString());
            return "";
        }

    }

    public int obtenerMayorID() {
        try {
            // connector.connect();
            ResultSet result = this.connector.executeStatementQuery("select MAX(idlinks)from links;");

            result.last();
            int t = result.getRow();
            if (result.getRow() == 1) {
                int i = result.getInt("MAX(idlinks)");
                return result.getInt(1);
            }
            
           // this.connector.disconnect();
            return -1;

        } catch (Exception ex) {
            System.out.println("ERROR al leer el maximo idlink");
            System.out.println(ex.toString());
            return -1;
        }
    }

    //############################################################################################################################################################
    //##################### Metodos usados en el modulo de indexación ##############################################################################
    public void obtenerVocabulario(Map<String, Palabra> terminos) throws Exception {
      //  this.connector.connect();

        ResultSet resultCantidadPalabras = connector.executeStatementQuery("SELECT COUNT(*) FROM vocabulario");
        resultCantidadPalabras.next();
        int cantidadPalabras = resultCantidadPalabras.getInt("Count(*)");

        PreparedStatement pstmt = connector.setPreparedStatement("SELECT * FROM vocabulario LIMIT 10000 OFFSET ?");

        for (int i = 0; i <= cantidadPalabras; i = i + 10000) {
            pstmt.setInt(1, i);
            ResultSet result = pstmt.executeQuery();

            Palabra palabra = null;

            while (result.next()) {
                palabra = new Palabra(result.getInt("id"), result.getString("termino"), result.getInt("cantDoc"), result.getInt("maxTf"));
                terminos.put(palabra.getTermino(), palabra);
            }
        }
      //  this.connector.disconnect();
    }

    public void grabarDocumento(Listener defListener) throws Exception {

      //  this.connector.connect();

        Connection coneccion = this.connector.getConnection();

        Statement stmt = coneccion.createStatement();
        //TODO ANALIZAR
        String path = defListener.doc.getAbsolutePath();
        path = path.replace("\\", "\\\\");

        ResultSet rs = stmt.executeQuery("select fn_addDocumento('" + path + "','" + defListener.doc.getName() + "')");
        rs.next();
        int idDocumentoGenerado = rs.getInt(1);
        //TODO UPDATE INNECESARIO???
        String qq = "update documento set cantPalabras = " + defListener.getNroPalabras() + " where id = " + idDocumentoGenerado;
        System.out.println(qq);
        stmt.executeUpdate(qq);
        
        //TODO SPLIT EN SP
        //Agregamos cada termino a la BD del vocabulario del documento.
        for (Map.Entry<String, Palabra> vocab : defListener.getVocabulario().entrySet()) {
            String cueri = "select agregar_palabra('" + vocab.getValue().getTermino() + "'," + vocab.getValue().getTf() + "," + idDocumentoGenerado + ")";
            System.out.println(cueri);
            rs = stmt.executeQuery(cueri);

            rs.next();
            int out = rs.getInt(1);

        }
     //   this.connector.disconnect();
    }

    //############################################################################################################################################################
    //##################### Metodos usados en el modulo de busqueda ##############################################################################
    public int obtenerTotalDocuemtos() throws Exception {
        //SqlConnector connector = new SqlConnector();
       // connector.connect();

        ResultSet result = connector.executeStatementQuery("SELECT count(*) as cantidad FROM documento");
        result.next();
        int n = result.getInt("cantidad");
        System.out.println(n);
     //   connector.disconnect();
        return n;
    }

    public ArrayList<Posteo> armarPosteos(ArrayList<Posteo> posteoList, Palabra palabra) throws Exception {
        //SqlConnector connector = new SqlConnector();
    //    connector.connect();

        //Armar posteos (new Posteo()) de esa palabra con sus documentos.
        Documento doc = null;
        ResultSet result = connector.executeStatementQuery("SELECT * FROM posteos p, documento d  WHERE p.idDoc = d.id AND p.idTermino = " + palabra.getId());

        while (result.next()) {
            doc = new Documento(result.getInt("id"), result.getString("nombre"), result.getString("path"), result.getInt("cantPalabras"));

            posteoList.add(new Posteo(doc, result.getInt("tf")));
        }
        return posteoList;
    }
    
    //############################################################################################################################################################
}
